<?php 

require_once 'config.php';

function getPoints($userId) {
	$mysqli = createMySQLi();
	$result = $mysqli->query("SELECT SUM(points_awarded) FROM user_act WHERE user_id = $userId AND status = 'DONE'");
	$array = $result != NULL && $result->num_rows > 0 ? $result->fetch_array() : NULL;
	$mysqli->close();
	return $array[0];
}

function getMoneyRaised($userId) {
	$mysqli = createMySQLi();
	$result = $mysqli->query("SELECT SUM(sponser_amount) FROM user_act WHERE user_id = $userId AND status = 'DONE'");
	$array = $result != NULL && $result->num_rows > 0 ? $result->fetch_array() : NULL;
	$mysqli->close();
	return $array[0];
}

/** 
 * Returns the activity or NULL if not found.
 */
function getActivity($actId) {
	$mysqli = createMySQLi();
	$result = $mysqli->query("SELECT * FROM activity WHERE activity_id = $actId LIMIT 1");
	$act = $result != NULL && $result->num_rows > 0 ? $result->fetch_assoc() : NULL;
	$mysqli->close();
	return $act;
}

/** Returns the user act or NULL if not found */
function getUserAct($actId, $userID) {
	$mysqli = createMySQLi();
	$result = $mysqli->query("SELECT * FROM user_act WHERE activity_id = $actId AND user_id = $userID LIMIT 1");
	$useract = $result != NULL && $result->num_rows > 0 ? $result->fetch_assoc() : NULL;
	$mysqli->close();
	return $useract;
}

function getActivities() {
	$mysqli = createMySQLi();
	$result = $mysqli->query("SELECT * FROM activity ORDER BY published_on DESC");
	$activities = array();
	if($result) {
		while($act = $result->fetch_assoc()) {
			$activities[] = $act;
		}
		$result->free();
	}
	$mysqli->close();
	return $activities;
}

/** creates the useract and return the id if successful otherwise the error string */
function createUserAct($activityID, $userID, $title, $points, $sponserTarget, $sponserAmount) {
	if(getUserAct($activityID, $userID) != NULL) {
		return "You've already registered to this activity.";
	}
	$sql = "INSERT INTO user_act (user_id, activity_id, title, points_awarded, sponser_target, sponser_amount) VALUES (
			$userID, $activityID, '$title', $points, '$sponserTarget', $sponserAmount)";
	
	$mysqli = createMySQLi();
	// echo $mysqli->get_server_info();
	$stmt = $mysqli->prepare($sql);
	// echo $sql;
	$stmt->execute();
	$affected_rows = $stmt->affected_rows;
	$errorStr = $affected_rows == 1 ? ($activityID > 0 ? $activityID : $mysqli->insert_id) : $mysqli->error;
	//echo $affected_rows . " " . $mysqli->error;
	$mysqli->close();
	return $errorStr;
}

/** updates the useract and return the id if successful otherwise the error string */
function completeUserAct($userActID) {
	$sql = "UPDATE user_act SET status = 'DONE' WHERE user_act_id = $userActID";
	$mysqli = createMySQLi();
	// echo $mysqli->get_server_info();
	$stmt = $mysqli->prepare($sql);
	// echo $sql;
	$stmt->execute();
	$affected_rows = $stmt->affected_rows;
	$errorStr = $affected_rows <= 1 ? ($userActID > 0 ? $userActID : $mysqli->insert_id) : $mysqli->error;
	//echo $affected_rows . " " . $mysqli->error;
	$mysqli->close();
	return $errorStr;
}

/**
 * INSERT/UPDATE activity, returns the act id if successful otherwise the error string.
 */
function saveActivity() {
	$activityID = $_REQUEST['activity_id'];
	$title = $_REQUEST['title'];
	$description = mysql_escape_string($_REQUEST['description']);
	$extract = mysql_escape_string($_REQUEST['extract']);
	$picture = $_REQUEST['picture'];
	$publishedOn = $_REQUEST['publishedOn'];
	$expiriedOn = $_REQUEST['expiriedOn'];
	$points = $_REQUEST['points'];
	$engagement = $_REQUEST['engagement'];
	$sponser = $_REQUEST['sponser'];
	$sponser_img = $_REQUEST['sponser_img'];
	$sponser_amount = $_REQUEST['sponser_amount'];
	
	if(!is_numeric($sponser_amount)) {
		$sponser_amount = '0';
	}
	if(!is_numeric($points)) {
		$points = '0';
	}
	$publishedOn = strlen($publishedOn) < 4 ? "NULL" : "'$publishedOn'";
	$expiriedOn = strlen($expiriedOn) < 4 ? "NULL" : "'$expiriedOn'";

	if($activityID > 0) { // UPDATE
		$sql = "UPDATE activity SET title = '$title', description = '$description', extract = '$extract', " . 
			"picture = '$picture', published_on = $publishedOn, expiried_on = $expiriedOn, " . 
			"points = $points, engagement = '$engagement', sponser = '$sponser', sponser_img = '$sponser_img', " .
			"sponser_amount = $sponser_amount WHERE activity_id = $activityID";
	} else { // INSERT
		$sql = "INSERT INTO activity (title, description, extract, picture, published_on, expiried_on, 
			points, engagement, sponser, sponser_img, sponser_amount) VALUES (
			'$title', '$description', '$extract', '$picture', $publishedOn, $expiriedOn,
			$points, '$engagement', '$sponser', '$sponser_img', $sponser_amount)";
	}
	$mysqli = createMySQLi();
	// echo $mysqli->get_server_info();
	$stmt = $mysqli->prepare($sql);
	// echo $sql;
	$stmt->execute();
	$affected_rows = $stmt->affected_rows;
	$errorStr = $affected_rows <= 1 ? ($activityID > 0 ? $activityID : $mysqli->insert_id) : $mysqli->error;
	//echo $affected_rows . " " . $mysqli->error;
	$mysqli->close();
	return $errorStr;
}




?>